﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
//using Microsoft.VisualStudio.Data;
using Microsoft.Data.ConnectionUI;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml.Linq;
using System.Data.EntityClient;

namespace Dashpart_Manager
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        VDashpartDataContext db;
        public MainWindow()
        {
            InitializeComponent();

            DBinit();
            //loadComboboxes();
        }

        private void DBinit()
        {
            db = new VDashpartDataContext();
            if (db.DatabaseExists())
            {
                loadComboboxes();
            }
        }

        private void loadComboboxes()
        {
            List<SEUser> employee = new List<SEUser>(); 

            if (chkActiveOnly.IsChecked == true)
            {
                employee = (from e in db.SEUsers
                            where e.DisableLogin == "N"
                            orderby e.Username
                           select e).ToList();
            }
            else
            {
                employee = (from e in db.SEUsers
                            orderby e.Username
                            select e).ToList();
            }

            
            cmbSelectEmployee.DataContext = employee.ToList();
            cmbSelectEmployee.DisplayMemberPath = "Username";
            cmbSelectEmployee.SelectedValuePath = "Username";
         

            cmbCopyToEmployee.ItemsSource = employee.ToList();
            cmbCopyToEmployee.DisplayMemberPath = "Username";
            cmbCopyToEmployee.SelectedValuePath = "Username";

            var role = from r in db.SEs
                       orderby r.Role
                       select r;

            cmbCopyToRole.ItemsSource = role;
            cmbCopyToRole.DisplayMemberPath = "Role";
            cmbCopyToRole.SelectedValuePath = "Role";


        }

        private void btnCopyToEmployee_Click(object sender, RoutedEventArgs e)
        {
            DashboardUser selectedDashpart = (DashboardUser)cmbSelectDashPart.SelectedItem;

            var userpart = (from d in db.DashboardUsers
                           where d.UserName == cmbCopyToEmployee.SelectedValue
                           && d.PartName == cmbSelectDashPart.SelectedValue
                                select d).ToList();
        
            DashboardUser du = new DashboardUser();
            //make sure the part doesnt' exist
            if (userpart.Count < 1)
            {
               du.ByRole = selectedDashpart.ByRole;
               du.Global = selectedDashpart.Global;
               du.Hidden = "N";
               du.ModDate = selectedDashpart.ModDate;
               du.PartKey = selectedDashpart.PartKey;
               du.PartName = selectedDashpart.PartName;
               du.PartType = selectedDashpart.PartType;
               du.UserName = cmbCopyToEmployee.SelectedValue.ToString();
               du.XML = selectedDashpart.XML;
            }

            db.DashboardUsers.InsertOnSubmit(du);

            db.SubmitChanges();

        }

        private void btnCopyToRole_Click(object sender, RoutedEventArgs e)
        {
            DashboardUser selectedDashpart = (DashboardUser)cmbSelectDashPart.SelectedItem;

            //get all users in role
            var users = from u in db.SEUsers 
                        where u.Role == cmbCopyToRole.SelectedValue.ToString()
                            select u;

            //loop throug users and create dashpart
            foreach (var item in users)
            {
                DashboardUser du = new DashboardUser();
                //make sure the part doesnt' exist
                var userpart = (from d in db.DashboardUsers
                                where d.UserName == item.Username
                                && d.PartName == cmbSelectDashPart.SelectedValue
                                select d).ToList();

                if (userpart.Count < 1)
                {
                    du.ByRole = selectedDashpart.ByRole;
                    du.Global = selectedDashpart.Global;
                    du.Hidden = "N";
                    du.ModDate = selectedDashpart.ModDate;
                    du.PartKey = selectedDashpart.PartKey;
                    du.PartName = selectedDashpart.PartName;
                    du.PartType = selectedDashpart.PartType;
                    du.UserName = item.Username;
                    du.XML = selectedDashpart.XML;
                }

                db.DashboardUsers.InsertOnSubmit(du);
            }
            db.SubmitChanges();
        }

        private void cmbSelectEmployee_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            var dashparts = from d in db.DashboardUsers
                            where d.UserName == cmbSelectEmployee.SelectedValue
                            select d;

            cmbSelectDashPart.ItemsSource = dashparts;
            cmbSelectDashPart.DisplayMemberPath = "PartName";
            cmbSelectDashPart.SelectedValuePath = "PartName";

        }


        private void chkActiveOnly_Checked(object sender, RoutedEventArgs e)
        {
            loadComboboxes();
        }

 

        private void btnRemoveRole_Click(object sender, RoutedEventArgs e)
        {
            DashboardUser selectedDashpart = (DashboardUser)cmbSelectDashPart.SelectedItem;

            //get all users in role
            var users = from u in db.SEUsers
                        where u.Role == cmbCopyToRole.SelectedValue.ToString()
                        select u;

            //loop throug users and create dashpart
            foreach (var item in users)
            {
                DashboardUser du = new DashboardUser();
                //make sure the part doesnt' exist
                var userpart = (from d in db.DashboardUsers
                                where d.UserName == item.Username
                                && d.PartName == cmbSelectDashPart.SelectedValue
                                select d).ToList();

                //make sure the part exists
                if (userpart.Count > 0)
                {
                    db.DashboardUsers.DeleteOnSubmit(userpart.FirstOrDefault()); 
                }

            }
            db.SubmitChanges();
        }

        private void btnRemovefromEmp_Clic(object sender, RoutedEventArgs e)
        {
            DashboardUser selectedDashpart = (DashboardUser)cmbSelectDashPart.SelectedItem;

            var userpart = (from d in db.DashboardUsers
                            where d.UserName == cmbCopyToEmployee.SelectedValue
                            && d.PartName == cmbSelectDashPart.SelectedValue
                            select d).ToList();

            //DashboardUser du = new DashboardUser();
            //make sure the part exists
            if (userpart.Count > 0)
            {
                db.DashboardUsers.DeleteOnSubmit(userpart.FirstOrDefault());
                db.SubmitChanges();
            }

        }

        private void btnSetDB_Click(object sender, RoutedEventArgs e)
        {

            //WpfApplication1.MainWindow mw = new WpfApplication1.MainWindow();
            //mw.Activate();
            //string conn = mw.Connectionstring;

            DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
            sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
            DataConnectionDialog dcd = new DataConnectionDialog();
            dcd.DataSources.Add(sqlDataSource);
            dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
            dcd.SelectedDataSource = sqlDataSource;
            DataConnectionDialog.Show(dcd);

            ChangeConnectionString("Dashpart_Manager.Properties.Settings.vision_liveConnectionString", dcd.ConnectionString.ToString());

            MessageBox.Show("Please close and reopen the applaction now.  Thank you.");

        }

        private bool ChangeEFConnectionString(string connStringName, string newValue)
        {
            try
            {
                //CreateXDocument and load configuration file
                XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                //Find all connection strings
                var query1 = from p in doc.Descendants("connectionStrings").Descendants()
                             select p;

                //Go through each connection string elements find atribute specified by argument and replace its value with newVAlue
                foreach (var child in query1)
                {
                    foreach (var atr in child.Attributes())
                    {
                        if (atr.Name.LocalName == "name" && atr.Value == connStringName)
                            if (atr.NextAttribute != null && atr.NextAttribute.Name == "connectionString")
                            {
                                // Create the EF connection string from existing
                                
                                EntityConnectionStringBuilder entityBuilder =
                                new EntityConnectionStringBuilder(atr.NextAttribute.Value);
                                //
                                entityBuilder.ProviderConnectionString = newValue;
                                //back the modified connection string to the configuration file
                                atr.NextAttribute.Value = entityBuilder.ToString();
                            }
                    }
                }

                doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        private bool ChangeConnectionString(string connStringName, string newValue)
        {
            try
            {
                //CreateXDocument and load configuration file
                XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                //Find all connection strings
                var query1 = from p in doc.Descendants("connectionStrings").Descendants()
                             select p;

                //Go throught each connection string elements find atribute specified by argument and replace its value with newVAlue
                foreach (var child in query1)
                {
                    foreach (var atr in child.Attributes())
                    {
                        if (atr.Name.LocalName == "name" && atr.Value == connStringName)
                            if (atr.NextAttribute != null && atr.NextAttribute.Name == "connectionString")
                                atr.NextAttribute.Value = newValue;
                    }
                }

                doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        private void btnConnectDB_Click(object sender, RoutedEventArgs e)
        {
            DBinit();
        }


    }
}
